package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KeyValueBean;
import jp.bsws.bean.TenpoBean;
import jp.bsws.common.BSWSConstants;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class TenpoDao {
	private static TenpoDao tenpoDao = new TenpoDao();

	/** ログ */
	private static Log log = LogFactory.getLog(TenpoDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static TenpoDao getInstance(){
		return tenpoDao;
	}

	private static String SQL_TENPO_CHANGE_SAKUJO_FLG =
		" UPDATE " +
		"     M_TENPO " +
		" SET " +
		"     DEL_FLG = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 店舗情報削除フラグ変更
	 */
	public int changeDelFlg(Connection con,Integer seq, String strDelFlg) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_TENPO_CHANGE_SAKUJO_FLG);
		stmt.setString(1, strDelFlg);
		stmt.setInt(2, seq);

		int updateCnt = stmt.executeUpdate();

		stmt.close();

		return updateCnt;
	}

	private static String SQL_TENPO_INSERT =
		" INSERT INTO " +
		"     M_TENPO ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,TENPO_COD " +
		"        ,TENPO_KNJ " +
		"        ,JUSYO_KNJ " +
		"        ,GYO_SU " +
		"        ,KAITEN_YMD " +
		"        ,HEITEN_YMD " +
		"        ,HYOJI_JUN " +
		"        ,DEL_FLG " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 店舗情報追加
	 */
	public int insertTenpo(Connection con, Integer seq, BSWSSession bSWSSession , TenpoBean tenpoBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//店舗番号の最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_TENPO");
		if(seq == null){
			seq = maxSeq + 1;
		}
		PreparedStatement stmt = con.prepareStatement(SQL_TENPO_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setString(3, bSWSSession.getStrGroupCod() + BSWSUtils.hidariUme(String.valueOf(seq + 1), 3, "0"));
		stmt.setString(4, tenpoBean.getStrTenpoKnj());
		stmt.setString(5, tenpoBean.getStrJusyoKnj());
		stmt.setInt(6, Integer.parseInt(tenpoBean.getStrGyoSu()));
		stmt.setInt(7, Integer.parseInt(tenpoBean.getStrKaitenYmd()));
		stmt.setInt(8, Integer.parseInt(tenpoBean.getStrHeitenYmd()));
		stmt.setInt(9, Integer.parseInt(tenpoBean.getStrHyojiJun()));
		stmt.setString(10, BSWSConstants.C_DEL_FLG_NOT_DEL);
		stmt.setString(11, bSWSSession.getStrJugyoinCod());
		stmt.setString(12, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private static String SQL_TENPO_UPDATE =
		" UPDATE " +
		"     M_TENPO " +
		" SET " +
		"         TENPO_KNJ = ? " +
		"        ,JUSYO_KNJ = ? " +
		"        ,GYO_SU = ? " +
		"        ,KAITEN_YMD = ? " +
		"        ,HEITEN_YMD = ? " +
		"        ,HYOJI_JUN = ? " +
		"        ,DEL_FLG = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 店舗情報更新
	 */
	public int updateTenpo(Connection con,String strJugyoinCod , Integer seq, TenpoBean tenpoBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_TENPO_UPDATE);

		stmt.setString(1, tenpoBean.getStrTenpoKnj());
		stmt.setString(2, tenpoBean.getStrJusyoKnj());
		stmt.setInt(3, Integer.parseInt(tenpoBean.getStrGyoSu()));
		stmt.setInt(4, Integer.parseInt(tenpoBean.getStrKaitenYmd()));
		stmt.setInt(5, Integer.parseInt(tenpoBean.getStrHeitenYmd()));
		stmt.setInt(6, Integer.parseInt(tenpoBean.getStrHyojiJun()));
		stmt.setString(7, BSWSConstants.C_DEL_FLG_NOT_DEL);
		stmt.setString(8, strJugyoinCod);
		stmt.setInt(9, seq);

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KV = " SELECT TENPO_COD,TENPO_KNJ FROM M_TENPO WHERE GROUP_COD = ? ";

	/**
	 * 店舗情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getTenpoList(Connection con, String pkGroupCod, String pkTenpoCod, String tenpoSetteiKbn) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = " AND DEL_FLG = ? ";
		if(BSWSConstants.C_TENPO_SANSYO_JI.equals(tenpoSetteiKbn)){
			if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
				where += " AND TENPO_COD = ? ";
			}
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KV + where);

		stmt.setString(1, pkGroupCod);
		stmt.setString(2, BSWSConstants.C_DEL_FLG_NOT_DEL);
		if(BSWSConstants.C_TENPO_SANSYO_JI.equals(tenpoSetteiKbn)){
			if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
				stmt.setString(3, pkTenpoCod);
			}
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("TENPO_COD");
			String value = rs.getString("TENPO_KNJ");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}
}